package db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Guest;
import model.RoomReservation;

public class DbRoomReservation {
	private Connection con;
	
	 public DbRoomReservation() {
		con = DbConnection.getInstance().getDBcon();
	}

	// Implements the methods from the interface
	// get all employees
	public ArrayList<RoomReservation> getAllRoomReservations(
			boolean retriveAssociation) {
		return miscWhere("", retriveAssociation);
	}

	private ArrayList<RoomReservation> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		ArrayList<RoomReservation> list = new ArrayList<RoomReservation>();

		String query = buildQuery(wClause);

		try { // read the employee from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				RoomReservation oObj = buildRoomReservation(results);
				list.add(oObj);
			}// end while
			stmt.close();
			if (retrieveAssociation) { // The supervisor and department is to be
										// build as well
				DbGuest dg = new DbGuest();
				for (RoomReservation resObj : list) {
					int guestId = resObj.getGuest().getId();
					Guest guest = dg.findGuest( guestId, false);
					resObj.setGuest(guest);
					guest.deleterReservation(resObj.getId());
					guest.addRoomReservation(resObj);
					System.out.println("Supervisor is seleceted");
					// here the department has to be selected as well
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT id, status, checkIn, checkOut, guestId, billId  FROM RoomReservation";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}

	private RoomReservation buildRoomReservation(ResultSet results)
    {   RoomReservation resObj = new RoomReservation();
        try{ // the columns from the table emplayee  are used
              resObj.setId(results.getInt("id"));
              resObj.setStatus(results.getString("status"));
              resObj.setCheckIn(results.getString("checkIn"));
              resObj.setCheckOut(results.getString("checkOut"));
              resObj.setGuest(new Guest(results.getInt("guestId")));
              
        }
       catch(Exception e)
       {
           System.out.println("error in building the RoomReservation object");
       }
       return resObj;
	

}
	
	//public RoomReservation findRoomReservation(int guestId, boolean b) {
	//	String query = "GuestId = " + guestId;
	//	return singleWhere(query, b);
	//}
	
	public int insert(RoomReservation rr) throws Exception {
		 //call to get the next ssn number
	    

	   int rc = -1;
	   String query="INSERT INTO RoomReservation(status, checkIn, checkOut, guestId)  VALUES('"+
	  		rr.getStatus()  + "','"  +
		    rr.getCheckIn()  + "','"  +
	  		rr.getCheckOut() + "','" +
		    rr.getGuest().getId() 
	  		 + "')";
	        
	        
	                 

	   System.out.println("insert : " + query);
	  try{ // insert new employee +  dependent
	      Statement stmt = con.createStatement();
	      stmt.setQueryTimeout(5);
	 	  rc = stmt.executeUpdate(query);
	      stmt.close();
	  }//end try
	   catch(SQLException ex){
	      System.out.println("Order ikke oprettet");
	      System.out.println("SQLException says: " + ex.getMessage());
	     throw new Exception ("Order is not inserted correct");
	   }
	   return(rc);
	}
}
